/*
MySQL Backup
Database: primary_secondary_registration_system
Backup Time: 2022-06-22 09:10:30
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`apply_info`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`apply_type`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`certificate_type`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`counties`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`fill_info`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`guardian`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`info_user_school_counties`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`patriarch_read`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`role`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`school`;
DROP TABLE IF EXISTS `primary_secondary_registration_system`.`user`;
CREATE TABLE `apply_info` (
  `apply_info_id` bigint(20) NOT NULL,
  `apply_type_id` bigint(20) DEFAULT NULL,
  `name` varchar(8) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `certificate_type_id` int(11) DEFAULT NULL,
  `certificate_num` varchar(18) DEFAULT NULL,
  `stu_img` varchar(255) DEFAULT NULL,
  `vaccination_imgs` varchar(255) DEFAULT NULL,
  `stu_preschool_education_imgs` varchar(255) DEFAULT NULL,
  `par_certificate_imgs` varchar(255) DEFAULT NULL,
  `poc_imgs` varchar(255) DEFAULT NULL,
  `rb_imgs` varchar(255) DEFAULT NULL,
  `apply_status` tinyint(4) DEFAULT NULL,
  `apply_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`apply_info_id`),
  KEY `apply_type_id` (`apply_type_id`),
  KEY `certificate_type_id` (`certificate_type_id`),
  CONSTRAINT `apply_info_ibfk_1` FOREIGN KEY (`apply_type_id`) REFERENCES `apply_type` (`apply_type_id`) ON DELETE CASCADE,
  CONSTRAINT `apply_info_ibfk_2` FOREIGN KEY (`certificate_type_id`) REFERENCES `certificate_type` (`certificate_type_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `apply_type` (
  `apply_type_id` bigint(20) NOT NULL,
  `apply_type_name` varchar(32) DEFAULT NULL,
  `apply_type_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `apply_type_grade` tinyint(4) DEFAULT NULL COMMENT '1:表示小学；2：表示初中；3：表示回长',
  PRIMARY KEY (`apply_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `certificate_type` (
  `certificate_type_id` int(11) NOT NULL,
  `certificate_type_name` varchar(32) DEFAULT NULL,
  `certificate_type_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`certificate_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `counties` (
  `counties_id` int(11) NOT NULL AUTO_INCREMENT,
  `counties_name` varchar(15) DEFAULT NULL,
  `counties_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`counties_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `fill_info` (
  `fill_info_id` bigint(20) NOT NULL,
  `fill_info_nation` varchar(24) DEFAULT NULL,
  `fill_info_resident_city` varchar(255) DEFAULT NULL,
  `fill_info_second_house` tinyint(4) DEFAULT NULL,
  `fill_info_house_certificate_type` varchar(64) DEFAULT NULL,
  `fill_info_house_certificate_num` varchar(64) DEFAULT NULL,
  `fill_info_house_person_name` varchar(12) DEFAULT NULL,
  `fill_info_person_certificate_type` varchar(32) DEFAULT NULL,
  `fill_info_house_person_certificate_num` varchar(18) DEFAULT NULL,
  `fill_info_house_site` varchar(255) DEFAULT NULL,
  `fill_info_house_relation_stu` varchar(8) DEFAULT NULL,
  `fill_info_house_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`fill_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `guardian` (
  `guardian_id` bigint(20) NOT NULL,
  `fill_info_id` bigint(20) DEFAULT NULL,
  `guardian_name` varchar(8) DEFAULT NULL,
  `guardian_relation` varchar(8) DEFAULT NULL,
  `guardian_certificate_type` varchar(32) DEFAULT NULL,
  `guardian_certificate_num` varchar(32) DEFAULT NULL,
  `guardian_phone` varchar(11) DEFAULT NULL,
  `guardian_work_unit` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`guardian_id`),
  KEY `fk_fill_info_guardian` (`fill_info_id`),
  CONSTRAINT `fk_fill_info_guardian` FOREIGN KEY (`fill_info_id`) REFERENCES `fill_info` (`fill_info_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `info_user_school_counties` (
  `id` bigint(20) NOT NULL,
  `apply_info_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `school_id` bigint(20) DEFAULT NULL,
  `counties_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gather_apply_info` (`apply_info_id`),
  KEY `fk_gather_user` (`user_id`),
  KEY `fk_gather_school` (`school_id`),
  KEY `fk_gather_counties` (`counties_id`),
  CONSTRAINT `fk_gather_apply_info` FOREIGN KEY (`apply_info_id`) REFERENCES `apply_info` (`apply_info_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gather_counties` FOREIGN KEY (`counties_id`) REFERENCES `counties` (`counties_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gather_school` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gather_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `patriarch_read` (
  `patriarch_read_id` int(11) NOT NULL,
  `patriarch_read_file` varchar(128) DEFAULT NULL,
  `partiarch_read_title` varchar(128) DEFAULT NULL,
  `partiarch_read_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`patriarch_read_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(5) DEFAULT NULL,
  `role_jurisdiction` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `school` (
  `school_id` bigint(20) NOT NULL,
  `counties_id` int(11) DEFAULT NULL,
  `school_name` varchar(64) DEFAULT NULL,
  `school_grade` tinyint(4) DEFAULT NULL,
  `school_range` text,
  `school_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `school_phone` char(11) DEFAULT NULL,
  PRIMARY KEY (`school_id`),
  KEY `counties_id` (`counties_id`),
  CONSTRAINT `school_ibfk_1` FOREIGN KEY (`counties_id`) REFERENCES `counties` (`counties_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL,
  `user_pwd` varchar(18) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `school_id` bigint(20) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  `user_status` tinyint(4) DEFAULT NULL,
  `user_create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `fk_user_role` (`role_id`),
  KEY `fk_user_school` (`school_id`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_school` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`apply_info` WRITE;
DELETE FROM `primary_secondary_registration_system`.`apply_info`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`apply_type` WRITE;
DELETE FROM `primary_secondary_registration_system`.`apply_type`;
INSERT INTO `primary_secondary_registration_system`.`apply_type` (`apply_type_id`,`apply_type_name`,`apply_type_date`,`apply_type_grade`) VALUES (1, '有房有户', '2022-06-21 20:45:13', 1),(2, '有户无房(第一类)', '2022-06-21 20:46:37', 1),(3, '有房无户', '2022-06-21 20:47:03', 1),(4, '有户无房（第二类)', '2022-06-21 20:47:42', 1),(5, '无房无户', '2022-06-21 20:54:27', 1);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`certificate_type` WRITE;
DELETE FROM `primary_secondary_registration_system`.`certificate_type`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`counties` WRITE;
DELETE FROM `primary_secondary_registration_system`.`counties`;
INSERT INTO `primary_secondary_registration_system`.`counties` (`counties_id`,`counties_name`,`counties_date`) VALUES (1, '岳麓区', '2022-06-20 00:00:00'),(4, '芙蓉区', '2020-02-10 00:00:00'),(7, '天心区', '2022-06-20 00:00:00'),(8, '开福区', '2022-06-20 00:00:00'),(9, '雨花区', '2022-06-20 00:00:00'),(10, '高新区', '2022-06-20 00:00:00');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`fill_info` WRITE;
DELETE FROM `primary_secondary_registration_system`.`fill_info`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`guardian` WRITE;
DELETE FROM `primary_secondary_registration_system`.`guardian`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`info_user_school_counties` WRITE;
DELETE FROM `primary_secondary_registration_system`.`info_user_school_counties`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`patriarch_read` WRITE;
DELETE FROM `primary_secondary_registration_system`.`patriarch_read`;
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`role` WRITE;
DELETE FROM `primary_secondary_registration_system`.`role`;
INSERT INTO `primary_secondary_registration_system`.`role` (`role_id`,`role_name`,`role_jurisdiction`) VALUES (1, '家长', 1);
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`school` WRITE;
DELETE FROM `primary_secondary_registration_system`.`school`;
INSERT INTO `primary_secondary_registration_system`.`school` (`school_id`,`counties_id`,`school_name`,`school_grade`,`school_range`,`school_date`,`school_phone`) VALUES (2, 4, '1号小学', 1, '1号小区范围', '2022-06-21 16:43:06', '82229950'),(3, 4, '3号小学', 1, '3号小区范围', '2022-06-21 16:43:16', '84281592'),(4, 4, '4号小学', 1, '4号小区范围', '2022-06-21 16:43:19', '82228840'),(5, 4, '5号初中', 2, '5号小区范围', '2022-06-21 16:43:35', '84453992'),(6, 4, '6号初中', 2, '6号小区范围', '2022-06-21 16:43:42', '82223913'),(7, 4, '7号初中', 2, '7号小区范围', '2022-06-21 16:43:49', '82565485'),(8, 7, '8号初中', 2, '8号小区范围', '2022-06-21 16:43:50', '85860067'),(9, 7, '9号初中', 2, '9号小区范围', '2022-06-21 16:44:05', '84452021'),(10, 7, '10号小学', 1, '10号小区范围', '2022-06-21 16:44:06', '85310228'),(11, 7, '11号小学', 1, '11号小区范围', '2022-06-21 16:44:15', '	85860067');
UNLOCK TABLES;
COMMIT;
BEGIN;
LOCK TABLES `primary_secondary_registration_system`.`user` WRITE;
DELETE FROM `primary_secondary_registration_system`.`user`;
UNLOCK TABLES;
COMMIT;
